from ToDo.todo_app_with_mysql.todo import *
import pymysql
class TodoManagerWithmysql:
    @staticmethod
    #连接数据库
    def get_connection():
        return pymysql.Connection(host='192.168.204.128', database='test1', user='root', password='Root@123456', autocommit=True)
    #获取表
    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            res = cursor.execute('''            
            CREATE table IF NOT EXISTS `Todos`(
            id INT PRIMARY key auto_increment,
            title VARCHAR(100) NOT NULL,
            completed INT NOT NULL DEFAULT 0)
            ''')
            return res
    #增加事项
    def create(self, title: str):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            self.init_db()
            # 返回表中受影响的行数, 如果元组里只有一个元素后面必须加逗号
            cursor.execute("insert into Todos(title) values(%s)", (title,))
            cursor.execute("select last_insert_id()")
            (id, ) = cursor.fetchone()
            return Todo(title, id=id)
            #res返回表中受影响的行数 num,如果元组里只有一个元素后面必须加逗号
            #res = cursor.execute("select version()")
            #取结果集中第一行数据以元组形式返回
            #data = cursor.fetchone()
            #取结果集中所有行数据以元组形式返回，元组套元组
            #data_one = cursor.fetchall()
    # 将数据变成对象
    def covert_row_Todo(self, i):
          return Todo(id=i[0], title=i[1], completed=False if i[2] == 0 else True)

    #所有事项
    def list_todo(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            self.init_db()
            cursor.execute("select * from Todos")
            # 取结果集中所有行数据以元组形式返回
            data = cursor.fetchall()
            todos = []
            for i in data:
                self.covert_row_Todo(i)
                todos.append(self.covert_row_Todo(i))
        return todos

    #通过ID获取数据
    def get_todo_byid(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from Todos where id=%s", (id,))
            todo = cursor.fetchone()
            if todo is not None:
                return self.covert_row_Todo(todo)
            else:
                return None
    #标记完成
    def mark_completed(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("update Todos set completed=1 where id=%s", (id,))
            return self.get_todo_byid(id)
    #删除数据
    def del_todo(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            # res返回表中受影响的行数 num,如果元组里只有一个元素后面必须加逗号
            cursor.execute("select * from Todos")
            cursor.fetchone()
            cursor.execute("delete from Todos where id=%s", (id,))
            conn.commit()
            return self.list_todo()
    #已完成事项
    def com_todo(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from Todos where completed=1")
            row = cursor.fetchall()
            one_com = []
            for i in row:
                one_com.append(self.covert_row_Todo(i))
            return one_com
    #未完成事项
    def not_com(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from Todos where completed=0")
            row = cursor.fetchall()
            two_com = []
            for i in row:
                two_com.append(self.covert_row_Todo(i))
            return two_com




# manager = TodoManagerWithmysql()
# print(manager.list_todo())








